package br.com.portalecbahia.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import br.com.portalecbahia.model.Media;
import br.com.portalecbahia.model.TypeMedia;
import br.com.portalecbahia.util.PortalECBahiaUtil;

public class MediaDAO {

	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;

	public MediaDAO(Context context) {
		this.context = context;
	}

	public MediaDAO openWritable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	public MediaDAO openReadable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getReadableDatabase();
		return this;
	}
	

	public void close() {
		dbHelper.close();
	}
	

	public List<Media> findAllAudio () {
		this.openReadable();

		List<Media> result = new ArrayList<Media>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT ID," +
									   "title, " +
									   "urlImage, " +
									   "time, "+
									   "date, " + 
									   "typeID, " +
									   "urlMedia "+
									   "FROM tbl_media " +
									   "where typeID = 1 " +
									   "order by date desc, id desc limit 0,20", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	public List<Media> findAllVideo () {
		this.openReadable();

		List<Media> result = new ArrayList<Media>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT ID," +
									   "title, " +
									   "urlImage, " +
									   "time, "+
									   "date, " +
									   "typeID, " +
									   "urlMedia "+
									   "FROM tbl_media " +
									   "where typeID = 2 " +
									   "order by date desc, id desc limit 0,20", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	public void insert(JSONArray jsonArray) throws JSONException, ParseException {
		this.openWritable();
		Cursor cursor = null;
		try{
			database.beginTransaction();
			for (int i = 0; i < jsonArray.length(); i++) {
				JSONObject json = jsonArray.getJSONObject(i);
	 		
				cursor = database.rawQuery(" INSERT INTO tbl_media (ID,  title, urlImage, time, date, urlMedia, typeID)  " +
							"VALUES (" + json.getLong("id")            +",  " +
									"'"+json.getString("title")        +"', "+
									"'"+json.getString("urlImageMedia")     +"', " +
									"'"+json.getString("time")    	   +"', " +
									"'"+json.getString("date") 	       +"', "+
									"'"+ json.getString("urlMedia")    +"', " +
									"" +json.getInt("typeID")+");", null);
					
					cursor.moveToFirst();
				
			}
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}finally {
			
			database.setTransactionSuccessful();
			database.endTransaction();
			
		}
		
		this.close();
		
	}
	
	
	
	public void removeAll() {
		this.openWritable();
		Cursor cursor = null;
		try{
			cursor = database.rawQuery("DELETE FROM tbl_media ", null);
			cursor.moveToFirst();
			cursor.close();
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		this.close();
		
	}

	

	private Media bindSQLite(Cursor cursor){
		Media media  = new Media();
		media.setType(new TypeMedia());
		SimpleDateFormat dateFormat = new  SimpleDateFormat("yyyy-MM-dd HH:mm");
		for (int i = 0; i < cursor.getColumnCount(); i++){
		
				if("ID".equals(cursor.getColumnName(i)))
					media.setId(cursor.getLong(i));
				else if("title".equals(cursor.getColumnName(i))) 
					media.setTitle(cursor.getString(i));
				else if("urlImage".equals(cursor.getColumnName(i))) 
					media.setUrlImage(cursor.getString(i));
				else if("time".equals(cursor.getColumnName(i))) 
					media.setTime(cursor.getString(i));
				else if("date".equals(cursor.getColumnName(i)))
					try {	
						media.setDate(dateFormat.parse(cursor.getString(i)));
					} catch (ParseException e) {
						Log.e(PortalECBahiaUtil.TAG, e.getMessage());
					}
				else if("urlMedia".equals(cursor.getColumnName(i))) 
					media.setUrlMedia(cursor.getString(i));
				else if("typeID".equals(cursor.getColumnName(i))) 
					media.getType().setId(cursor.getLong(i));
				
		}
		return media;
	}
}
